﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Objects;
using System.Data.SqlClient;

namespace CustomerService
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Session["OwnerID"] = 1;
        }

        protected void btnNewCustomer_Click(object sender, EventArgs e)
        {
            SalesManagerEntities sme = new SalesManagerEntities();
            Session["sme"] = sme;

            tblCustomer newCustomer = new tblCustomer();
            newCustomer.OwnerID = 1;
            sme.AddTotblCustomer(newCustomer);
            newCustomer.Person = new tblPerson();
            newCustomer.Person.Address = new tblAddress();

            sme.SaveChanges();

            Response.Redirect("EditCustomer.aspx?CustomerID=" + newCustomer.CustomerID.ToString());
        }

        protected void btnLoadCustomer_Click(object sender, EventArgs e)
        {
            try
            {
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                SalesManagerEntities sme = new SalesManagerEntities();
                sme.ContextOptions.LazyLoadingEnabled = true;
                Session["sme"] = sme;

                List<CustomerSearch> custList = null;

                if (txtTextSearch.Text.Trim() != string.Empty)
                {
                    ObjectResult<CustomerSearch> results = sme.ExecuteStoreQuery<CustomerSearch>("select null as SearchKeyType, T1.CustomerID, T3.Name, T4.Address1, T4.City, T4.State, T4.PostalCode, T5.OrderDate\n" +
                        "from tblCustomer T1\n" +
                        "   join ContainsTable(tblTextSearch, SearchKey, @SearchKey) as T2 on T1.CustomerID = T2.[key]\n" +
                        "   join tblPerson T3 on T1.PersonID = T3.PersonID\n" +
                        "   left outer join tblAddress T4 on T3.AddressID = T4.AddressID\n" +
                        "   left outer join ( select CustomerID, max(OrderDate) as OrderDate from tblOrderHeader group by CustomerID ) as T5 on T1.CustomerID = T5.CustomerID\n" +
                        "order by T3.Name", new SqlParameter[] { new SqlParameter("@SearchKey", txtTextSearch.Text) });
                    custList = results.ToList();
                }
                else if (txtIDSearch.Text.Trim() != string.Empty)
                {
                    ObjectResult<CustomerSearch> results = sme.ExecuteStoreQuery<CustomerSearch>("select T6.Description as SearchKeyType, T1.CustomerID, T3.Name, T4.Address1, T4.City, T4.State, T4.PostalCode, T5.OrderDate\n" +
                        "from tblCustomer T1\n" +
                        "   join tblIDSearch as T2 on T1.CustomerID = T2.CustomerID\n" +
                        "   join tblPerson T3 on T1.PersonID = T3.PersonID\n" +
                        "   left outer join tblAddress T4 on T3.AddressID = T4.AddressID\n" +
                        "   left outer join ( select CustomerID, max(OrderDate) as OrderDate from tblOrderHeader group by CustomerID ) as T5 on T1.CustomerID = T5.CustomerID\n" +
                        "   join codeIDSearchKey T6 on T2.IDSearchKeyCode = T6.IDSearchKeyCode\n" +
                        "where T2.IDValue = cast(@SearchKey as varchar)\n" +
                        "order by T3.Name", new SqlParameter[] { new SqlParameter("@SearchKey", txtIDSearch.Text) });
                    custList = results.ToList();
                }
                //System.Data.Objects.ObjectQuery<tblCustomer> q = sme.tblCustomer.Include("Person")
                //    .Include("Person.Address")
                //    .Include("Person.ContactPoints");
                //switch (cmbSearchBy.SelectedValue)
                //{
                //    case "CustomerID":
                //        int customerID;
                //        if (int.TryParse(txtSearchValue.Text, out customerID))
                //            custList = from c in q where c.CustomerID==customerID select c;
                //        else
                //            sb.AppendLine("Invalid customer ID");
                //        break;
                //    case "OrderID":
                //        int orderID;
                //        if (int.TryParse(txtSearchValue.Text, out orderID))
                //            custList = from c in q where c.OrderHeaders.Any(oh => oh.OrderID == orderID) select c;
                //        else
                //            sb.AppendLine("Invalid order ID");
                //        break;
                //    case "Phone":
                //        custList = 
                //            (from c in q 
                //             where c.Person.ContactPoints.Any(cp=>cp.Value.Contains(txtSearchValue.Text)) select c);
                //        break;
                //    case "Zip":
                //        custList =
                //            (from c in q
                //             where c.Person.Address.Zip.Contains(txtSearchValue.Text) select c);
                //        break;
                //    case "City":
                //        string[] citystate = txtSearchValue.Text.Split(new char[] { ',' });
                //        string city = citystate[0].Trim();
                //        string state = string.Empty;
                //        if (citystate.Length > 1)
                //            state = citystate[1].Trim();
                //        custList =
                //            (from c in q
                //             where c.Person.Address.City.Contains(city)
                //                && c.Person.Address.State.Contains(state)
                //             select c).Union(
                //             from c in q
                //             where c.OrderHeaders.Any(oh=>oh.ShipTo_Address.City.Contains(city) && oh.ShipTo_Address.State.Contains(state))
                //             select c);
                //        break;
                //    case"Last":
                //        custList =
                //            from c in q
                //            where c.Person.LastName.Contains(txtSearchValue.Text)
                //            select c;
                //        break;
                //}
                if (custList != null)
                {
                    int ct = custList.Count();
                    if (ct == 1)
                        Response.Redirect("EditCustomer.aspx?CustomerID=" + custList.First().CustomerID.ToString());
                    if (ct > 1)
                    {
                        Session["CustomerList"] = custList;
                        Response.Redirect("CustomerList.aspx");
                    }
                    if (ct == 0)
                    {
                        sb.AppendLine("No matching customers found");
                    }
                }
                lblErrorMessage.Text = sb.ToString();
            }
            catch (Exception err)
            {
                lblErrorMessage.Text = err.Message;
            }
        }
}
}
